
const Sequelize = require('sequelize')
const db = require('./')
const Op = Sequelize.Op

module.exports = (sequelize, DataTypes) => {
  const tagImage = sequelize.import('./', require('../models/m_tag_image_map'))

  class m_images extends Sequelize.Model {


    /**
     * @author: Shihh
     * @description: 查询分页带出图片 标签TagName
     * @param {type} 
     * @return: 
     */
    static async getListTags(params) {
      var [page = 1, pagesize = 12, order = null, where = null] = [params.page, params.pagesize, params.order, params.where]
      let condition = {
        raw: true,
        attributes: ['*'],
        offset: pagesize * (page - 1),
        limit: parseInt(pagesize),
      }
      condition.where = where
      if (order !== null) condition.order = order
      var result = await this.findAndCountAll(condition);
      return result
    }


    /**
     * @author: xgq
     * @description: 批量上/下架
     * @param {type} 
     * @return: 
     */
    static async imagesUpORDown(pars) {
      let result = '';
      // let sql = `update images SET is_enable=1 where id in (?)`
      // result = await sequelize.query(sql,{replacements:[ids]})
      result = await this.update({ is_enable: pars.status }, { where: { id: { [Op.in]: pars.ids } } })
      return result;
    }



    static getList(pars) {
      var [page = 1, pagesize = 12, order = null, where = null] = [pars.page, pars.pagesize, pars.order, pars.where]
      let condition = {
        attributes: ['id', 'size', 'img_name', 'imgsid', 'img_src', 'img_thumbnail', 'img_designer', 'category', 'e_category', 'prefix_path', 'oss_prepath', 'download_counts', 'watch_num', 'like_num', 'create_time', 'file_size', 'is_enable'],
        offset: pagesize * (page - 1),
        raw: true,
        limit: parseInt(pagesize)
      }
      if (where !== null) condition.where = where
      if (order !== null) {
        condition.order = order
      }
      return this.findAndCountAll(condition);
    }

    // 根据图片分类ID查找image
    static async getImgIdsByCategoryId(pars) {
      let querydata = await this.findAll({ attributes: ['id'], where: { category: id } })
      var arr = []
      for (var i in querydata) {
        arr.push(querydata[i].dataValues.id)
      }
      return arr
    }

    // 读取图片id,整理成一个数组
    static async readImageIds(arr, att) {
      var r = [];
      for (var obj of arr) {
        r.push(obj[att])
      }
      return r;
    }

    // 获取最热（用户行为最多）的图片列表
    static async getMostHot(pars) {
      var [page = 1, pagesize = 12, limit = 0] = [pars.page, pars.pagesize, pars.limit]
      let imageList = await this.findAll({
        attributes: ['prefix_path', 'img_name', 'id', 'img_thumbnail', 'oss_prepath', 'e_category', 'category', 'img_designer', 'size', 'create_time', 'download_counts', 'watch_num', 'is_enable', 'file_size', 'like_num'],
        raw: true,
        order: [['download_counts', 'desc'], ['like_num', 'desc']],
        offset: pagesize * (page - 1),
        limit: parseInt(pagesize)
      })
      return { "rows": imageList };
    }




    // 获取下载量最多的图片
    static async fornt_getMostDown(pars) {
      var querydata = [];
      var [page = 1, pagesize = 12, limit = 0] = [pars.page, pars.pagesize, pars.limit]
      var where = {
        is_enable: 0,
      }
      let condition = {
        attributes: ['prefix_path', 'img_name', 'id', 'img_thumbnail', 'oss_prepath', 'e_category', 'category', 'img_designer', 'size', 'create_time', 'download_counts', 'watch_num', 'is_enable', 'file_size', 'like_num'],
        offset: pagesize * (page - 1),
        raw: true,
        limit: parseInt(pagesize)

      }
      condition.where = where
      condition.order = [
        ['download_counts', 'DESC']
      ]
      querydata = await this.findAndCountAll(condition);
      return querydata //{ "rows": querydata};
    }



    static async update_is_enable(pars) {
      var result = this.update({ is_enable: pars.is_enable }, { where: { id: pars.id } })
      return result;
    }

    //根据id获取图片详情
    static async getDetailsById(pars) {
      let condition = {
        include: [{
          model: (tagImage),
          as: 'ti',
          attributes: [['tag_name', 'tags']],
          attributes: [
            [sequelize.fn('GROUP_CONCAT', sequelize.col('tag_name')), 'tag_name']
          ],
          required: false
        }],
        raw: true,
        attributes: ['id', 'url', 'size', 'img_name', 'imgsid', 'img_src', 'img_thumbnail', 'img_designer', 'category', 'e_category', 'prefix_path', 'oss_prepath', 'download_counts', 'watch_num', 'like_num', 'create_time', 'file_size', 'is_enable', 'tagIds'],
        where: {
          id: pars.id,
          is_enable: pars.is_enable
        },
      }
      var result = this.findOne(condition)
      return result;
    }
    /**
     * 获取相关图片
     * @param {*} pars 
     */
    static async getImagesLikely(pars) {
      let { category_id, tag_id, page, pagesize } = pars
      let offset = pagesize * (page - 1)
      let sql = "select * from images where id in (select distinct(images.id) from images left join tag_image_map ti on images.id = ti.image_id where images.category = :category_id and ti.tag_id in (:tag_id) and images.is_enable=0  order by images.download_counts DESC) limit :offset,:pagesize "
      let imageList = await sequelize.query(
        sql, //原生 SQL 语句，参数用 ? 
        { replacements: { category_id: category_id, tag_id: tag_id, offset: offset, pagesize: parseInt(pagesize) }, type: Sequelize.QueryTypes.SELECT } //replacements替换SQL中的参数
      );
      let countSql = "select count (*)  as count from (select distinct(images.id) from images  left join tag_image_map ti on images.id = ti.image_id where images.category = :category_id and ti.tag_id in (:tag_id)  and images.is_enable=0 ) as ids"
      let count = await sequelize.query(
        countSql, //原生 SQL 语句，参数用 ? 
        { replacements: { category_id: category_id, tag_id: tag_id }, type: Sequelize.QueryTypes.SELECT } //replacements替换SQL中的参数
      );
      var result = {
        rows: imageList,
        count: count[0].count
      }
      return result;
    }
    /**
     * 根据标签ID获取图片
     * @param {*} pars 
     */
    static async getImagesByTagId(pars) {
      let { tag_id, page, pagesize } = pars
      let offset = pagesize * (page - 1)
      let sql = "select images.* from images left join tag_image_map ti on images.id = ti.image_id where   ti.tag_id = :tag_id and images.is_enable=0  order by images.download_counts DESC limit :offset,:pagesize "
      let imageList = await sequelize.query(
        sql, //原生 SQL 语句，参数用 ? 
        { replacements: { tag_id: tag_id, offset: offset, pagesize: parseInt(pagesize) }, type: Sequelize.QueryTypes.SELECT } //replacements替换SQL中的参数
      );
      let countSql = "select count(*) as count from images left join tag_image_map ti on images.id = ti.image_id where   ti.tag_id = :tag_id and images.is_enable=0 "
      let count = await sequelize.query(
        countSql, //原生 SQL 语句，参数用 ? 
        { replacements: { tag_id: tag_id }, type: Sequelize.QueryTypes.SELECT } //replacements替换SQL中的参数
      );
      var result = {
        rows: imageList,
        count: count[0].count
      }
      return result;
    }
    /**
    * 根据多个标签ID获取图片
    * @param {*} pars 
    */
    static async getImagesByTags(pars) {
      let { tagIds, page, pagesize } = pars
      let offset = pagesize * (page - 1)
      let sql = "select * from images where id in (select DISTINCT(images.id) from images left join tag_image_map ti on images.id = ti.image_id where   ti.tag_id in (:tagIds) and images.is_enable=0  order by images.download_counts DESC) limit :offset,:pagesize"
      let imageList = await sequelize.query(
        sql, //原生 SQL 语句，参数用 ? 
        { replacements: { tagIds: tagIds, offset: offset, pagesize: parseInt(pagesize) }, type: Sequelize.QueryTypes.SELECT } //replacements替换SQL中的参数
      );
      let countSql = " select count(*) as count from (select DISTINCT(images.id) from images left join tag_image_map ti on images.id = ti.image_id where   ti.tag_id in (:tagIds) and images.is_enable=0 ) as ids "
      let count = await sequelize.query(
        countSql, //原生 SQL 语句，参数用 ? 
        { replacements: { tagIds: tagIds }, type: Sequelize.QueryTypes.SELECT } //replacements替换SQL中的参数
      );
      var result = {
        rows: imageList,
        count: count[0].count
      }
      return result;
    }
    /**
     * 专题的第三种，根据图片ID组获取图片
     * @param {}} pars 
     */
    static async getListByImgIds(pars) {
      let { imgIds, page, pagesize } = pars
      let offset = pagesize * (page - 1)
      let sql = "select * from images  where  id in (:imgIds) and is_enable=0  order by download_counts DESC limit :offset,:pagesize "
      let imageList = await sequelize.query(
        sql, //原生 SQL 语句，参数用 ? 
        { replacements: { imgIds: imgIds, offset: offset, pagesize: parseInt(pagesize) }, type: Sequelize.QueryTypes.SELECT } //replacements替换SQL中的参数
      );
      let countSql = "select count(*) as count from images  where   id in (:imgIds) and is_enable=0 "
      let count = await sequelize.query(
        countSql, //原生 SQL 语句，参数用 ? 
        { replacements: { imgIds: imgIds }, type: Sequelize.QueryTypes.SELECT } //replacements替换SQL中的参数
      );
      var result = {
        rows: imageList,
        count: count[0].count
      }
      return result;
    }
    /**
     * 根据关键字获取图片
     */
    static async getListByKeyWords(pars) {
      let { tagIds, categoryIds, page, pagesize } = pars
      let offset = pagesize * (page - 1)
      if(tagIds.length<1){
        var result = {
          rows: [],
          count: 0
        }
        return result;
      }
      let sql = "select * from images where id in (select DISTINCT(images.id) from images left join tag_image_map ti on images.id = ti.image_id where   ti.tag_id in (:tagIds) and images.is_enable=0  order by images.download_counts DESC) limit :offset,:pagesize"
      let imageList = await sequelize.query(
        sql, //原生 SQL 语句，参数用 ? 
        { replacements: { tagIds: tagIds, offset: offset, pagesize: parseInt(pagesize) }, type: Sequelize.QueryTypes.SELECT } //replacements替换SQL中的参数
      );
      let countSql = " select count(*) as count from (select DISTINCT(images.id) from images left join tag_image_map ti on images.id = ti.image_id where   ti.tag_id in (:tagIds) and images.is_enable=0 ) as ids "
      let count = await sequelize.query(
        countSql, //原生 SQL 语句，参数用 ? 
        { replacements: { tagIds: tagIds }, type: Sequelize.QueryTypes.SELECT } //replacements替换SQL中的参数
      );
      var result = {
        rows: imageList,
        count: count[0].count
      }
      return result;

    }
    /**
     * @author: xgq
     * @description: 编辑图片
     * @param {type} 
     * @return: 
     */
    static async updateImage(p) {
      let result = await this.update({
        url: p.url,
        img_name: p.img_name,
        watch_num: p.watch_num,
        download_counts: p.download_counts,
        like_num: p.like_num,
        img_designer: p.img_designer,
        describe: p.describe
      }, { where: { id: p.id } })
      return result
    }

  }

  m_images.init({
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    url: { type: DataTypes.STRING(255) },
    size: { type: DataTypes.STRING(255) },
    img_name: { type: DataTypes.STRING(255) },
    imgsid: { type: DataTypes.STRING(64) },
    img_src: { type: DataTypes.STRING(255) },
    img_thumbnail: { type: DataTypes.STRING(255) },
    img_designer: { type: DataTypes.STRING(128) },
    category: { type: DataTypes.INTEGER },
    e_category: { type: DataTypes.STRING(64) },
    prefix_path: { type: DataTypes.STRING(255) },
    oss_prepath: { type: DataTypes.STRING(255) },
    download_counts: { type: DataTypes.INTEGER },
    watch_num: { type: DataTypes.INTEGER },
    create_time: { type: DataTypes.INTEGER },
    is_enable: { type: DataTypes.BOOLEAN },
    file_size: { type: DataTypes.INTEGER },
    like_num: { type: DataTypes.INTEGER },
    tagIds: { type: DataTypes.STRING(511) },
    describe: { type: DataTypes.STRING(256) }
  }, {
    sequelize,
    modelName: 'images',
    freezeTableName: true,
    timestamps: false
  });
  m_images.hasMany(tagImage, { as: 'ti', foreignKey: 'image_id', targetKey: 'id' });


  return m_images;
}